-- B1 - Query 1 - Inverted boolean index.
DROP TABLE IF EXISTS words_boolean_index;
CREATE TABLE IF NOT EXISTS words_boolean_index AS
SELECT DISTINCT id, word FROM 
(
	SELECT id, lower(word) word FROM words
	WHERE word ~* '^[a-z][a-z0-9_]+$'
	AND tablename = 'posts' AND (what='title' OR what='body')
	GROUP BY id, word
) a
GROUP BY id, word;

-- B1 - Query 2 - Exact match function.
DROP FUNCTION IF EXISTS exact_match();
CREATE OR REPLACE FUNCTION exact_match(VARIADIC w text[])
RETURNS TABLE (id integer) AS
$$
DECLARE t text := '';
BEGIN
FOR i IN 1..ARRAY_LENGTH(w,1)
    LOOP
        t := t || 'SELECT id FROM words_boolean_index WHERE word = ' || '''' || w[i] || '''';
            IF (i!=array_length(w,1)) THEN
                t:= t || ' INTERSECT ';
            END IF;
    END LOOP;
t := t || ';';
RETURN QUERY EXECUTE t;
END $$
LANGUAGE 'plpgsql';
SELECT * FROM exact_match('code', 'visual', 'studio');

-- B2 - Query 1 - Refined best match function with ranking.
DROP FUNCTION IF EXISTS best_match_ranked();
CREATE OR REPLACE FUNCTION best_match_ranked(VARIADIC w text[])
RETURNS TABLE (id integer, rank bigint) AS
$$
DECLARE t text := 'SELECT id, sum(score) AS rank FROM (';
BEGIN
FOR i IN 1..array_length(w,1)
    LOOP
        t := t || 'SELECT DISTINCT id, 1 score FROM words_boolean_index WHERE word = ' || '''' || w[i] || '''';
            IF (i!=array_length(w,1)) THEN
                t:= t || ' UNION ALL ';
            END IF;
    END LOOP;
t := t || ') AS ranked_words GROUP BY id ORDER BY rank DESC;';
RETURN QUERY EXECUTE t;
END $$
LANGUAGE 'plpgsql';
SELECT * FROM best_match_ranked('visual', 'studio', 'code');

-- B3 - Query 1 - Weighted index.
DROP TABLE IF EXISTS document_count;
CREATE TABLE document_count AS
SELECT word, count(*) document_count FROM words_boolean_index
GROUP BY word;

-- B3 - Query 2 - 
DROP TABLE IF EXISTS term_count;
CREATE TABLE term_count AS
SELECT id, count(word) term_count FROM words
GROUP BY id;

-- B3 - Query 3 - Inverted boolean index with frequency.
DROP TABLE IF EXISTS words_frequency_index;
CREATE TABLE IF NOT EXISTS words_frequency_index AS
SELECT id, word, sum(frequency) frequency FROM
(
	SELECT id, lower(word) word, count(*) frequency FROM words
	WHERE word ~* '^[a-z][a-z0-9_]+$'
	AND tablename = 'posts' AND (what='title' OR what='body')
	GROUP BY id, word
) a
GROUP BY id, word;

-- B3 - Query 4 - Ranked word index (tfidf)
DROP TABLE IF EXISTS words_ranked_index;
CREATE TABLE  words_ranked_index AS
SELECT id, word, frequency, document_count, term_count,
   (log(1::decimal + (frequency::DECIMAL/term_count::DECIMAL))) tf,
   (1::decimal / document_count::decimal) idf,
   (log(1::decimal + (frequency::DECIMAL/term_count::DECIMAL)))*(1::decimal / document_count::decimal) tfidf
FROM words_frequency_index
   NATURAL JOIN document_count
   NATURAL JOIN term_count;
SELECT * FROM words_ranked_index;

-- B4 - Query 1 - Best match weighted by rank (tfidf)
DROP FUNCTION IF EXISTS best_match_weigthed_rank();
CREATE OR REPLACE FUNCTION best_match_weigthed_rank(VARIADIC w text[])
RETURNS TABLE (id integer, rank_weight NUMERIC) AS
$$
DECLARE t text := 'SELECT id, sum(tfidf) AS rank_weight FROM words_ranked_index WHERE word = ';
BEGIN
FOR i IN 1..array_length(w,1)
    LOOP
        t := t || '''' || w[i] || '''';
            IF (i!=array_length(w,1)) THEN
                t:= t || ' OR word = ';
            END IF;
    END LOOP;
t := t || ' GROUP BY id ORDER BY rank_weight DESC;';
RETURN QUERY EXECUTE t;
END $$
LANGUAGE 'plpgsql';
SELECT * FROM best_match_weigthed_rank('visual', 'studio', 'code', 'java');

-- B5 - Query 3 - Weighted keywords list by frequency
DROP FUNCTION IF EXISTS weighted_keywords();
CREATE OR REPLACE FUNCTION weighted_keywords(VARIADIC w text[])
RETURNS TABLE (word TEXT, frequency_sum NUMERIC) AS
$$
DECLARE t text := 'SELECT word, sum(frequency) frequency_sum from words_frequency_index WHERE id IN (';
BEGIN
FOR i IN 1..array_length(w,1)
    LOOP
        t := t || 'SELECT distinct id from words_frequency_index where word = ' || '''' || w[i] || '''';
            IF (i!=array_length(w,1)) THEN
                t:= t || ' UNION ALL ';
            END IF;
    END LOOP;
t := t || ') AND word NOT IN (SELECT word FROM stopwords) GROUP BY word ORDER BY frequency_sum DESC;';
RAISE NOTICE 'Query: %', t;
RETURN QUERY EXECUTE t;
END $$
LANGUAGE 'plpgsql';
SELECT * FROM weighted_keywords('java', 'android');

-- B6 - Word cloud visualization
SELECT frequency_sum || ' ' || word FROM weighted_keywords('search','text');

-- B7 - Query 1
DROP TABLE IF EXISTS words_cooccurrence;
CREATE TABLE IF NOT EXISTS words_cooccurrence AS
SELECT w1.word AS term1, w2.word AS term2, count(*) occurrences FROM words_ranked_index w1, words_ranked_index w2
WHERE w1.id = w2.id
AND w1.word < w2.word
AND w1.tfidf > 0.0000195
AND w2.tfidf > 0.0000195
AND w1.term_count > 20
AND w2.term_count > 20
GROUP BY w1.word, w2.word;
SELECT * FROM words_cooccurrence ORDER BY occurrences DESC;


-- B7 - Query 2
DROP FUNCTION IF EXISTS get_word_context();
CREATE OR REPLACE FUNCTION get_word_context(term TEXT)
RETURNS TABLE (context TEXT) AS
$$
DECLARE q TEXT := '';
BEGIN
	q := q || 'SELECT term FROM (';
	q := q || '(SELECT term1 AS term, occurrences FROM words_cooccurrence WHERE term2 = ''' || term || ''')';
	q := q || ' UNION ';
	q := q || '(SELECT term2 AS term, occurrences FROM words_cooccurrence WHERE term1 = ''' || term || ''')';
	q := q || ') AS result ORDER BY result.occurrences DESC;';
	RETURN QUERY EXECUTE q;
END
$$
LANGUAGE 'plpgsql';
SELECT get_word_context('microsoft');

-- C -- creating indexes and optimizing
CREATE INDEX wbi_index ON words_boolean_index(word);
CREATE INDEX wri_index ON words_ranked_index(word);
CREATE INDEX wfi_index ON words_frequency_index(word);
CREATE INDEX wc_index ON words_cooccurrence(term1, term2);

-- D -- improvement OF the search function
DROP FUNCTION IF EXISTS search();
CREATE OR REPLACE FUNCTION search(search_text text, account_id integer)
 RETURNS TABLE(id integer, title text, body text, author_id integer, accepted_answer_id integer, closed_date timestamp without time zone, creation_date timestamp without time zone, score integer)
AS $$
DECLARE words text[] := regexp_split_to_array(lower(search_text), '\s+');
BEGIN
INSERT INTO search(search_text, search_date, account_id) VALUES(search_text, NOW() AT TIME ZONE 'UTC', account_id);

RETURN QUERY
	SELECT best_match_weigthed_rank.id, question.title, question.body, author.id, question.accepted_answer_id, question.closed_date, question.creation_date, question.score
	FROM best_match_weigthed_rank(VARIADIC w := words)
	JOIN question ON best_match_weigthed_rank.id = question.id
	JOIN author ON question.author_id=author.id
	ORDER BY best_match_weigthed_rank.rank_weight DESC;
END;
$$
LANGUAGE plpgsql;
SELECT * FROM search('visual studio', 1);
